from sqlalchemy import Column, Integer, String, DateTime, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import relationship
from sqlalchemy.sql import func
from api.database import Base

class EmailFolder(Base):
    """
    邮件-文件夹关联表
    实现邮件与文件夹的多对多关系（快照式管理）
    """
    __tablename__ = "email_folders"
    
    # 主键
    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    
    # 外键关联
    email_id = Column(Integer, ForeignKey("emails.id", ondelete="CASCADE"), nullable=False)
    email_account_id = Column(Integer, ForeignKey("email_accounts.id"), nullable=False)
    
    # 文件夹信息（直接存储文件夹名称，无外键）
    folder_name = Column(String(255), nullable=False)
    
    # IMAP 特定信息（每个文件夹视图下的唯一标识）
    uid = Column(String(100), nullable=False)
    uidvalidity = Column(String(50))
    imap_key = Column(String(128))  # folder|uid|uidvalidity
    
    # IMAP FLAGS
    flags = Column(String(255))
    
    # 时间戳
    synced_at = Column(DateTime, default=func.now())
    updated_at = Column(DateTime, default=func.now(), onupdate=func.now())
    
    # 关系定义
    email = relationship("Email", back_populates="folders")
    account = relationship("EmailAccount")
    
    # 约束和索引
    __table_args__ = (
        # 同一邮件在同一账户的同一文件夹只能有一条记录
        UniqueConstraint('email_id', 'email_account_id', 'folder_name', 
                        name='uq_email_account_folder'),
        # IMAP 操作索引
        Index('idx_ef_account_folder', 'email_account_id', 'folder_name'),
        Index('idx_ef_folder_uid', 'email_account_id', 'folder_name', 'uid'),
        Index('idx_ef_imap_key', 'imap_key'),
        Index('idx_ef_email_id', 'email_id'),
    )
    
    def __repr__(self):
        return f"<EmailFolder(id={self.id}, email_id={self.email_id}, folder='{self.folder_name}', uid='{self.uid}')>"